package com.kcs.dc.service.utils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import com.kcs.common.utils.Constant;
import com.kcs.common.utils.LogWriter;

/**
 * @author 	Vanida Anupat (Bee)
 *
 * Revision:
 * <No> <12/12/2012/15:48><Reference>
 * 	<For write log on table : Can modifies structure of this table and logic insert.>
 */

public class LogToDB {

	private static final String TBLLOG = "SERVICE_LOG";
        
    private static final String INSERT_REQ = ""
            + "INSERT INTO " + TBLLOG + "("
            + "REQ_TIME, REQ_SYSTEM_ID, REQ_SERVICE_ID, REQ_ID, REQ_NO_OF_REC, HEAD_REQ_DEALID, HEAD_REQ_CIFNO,"
            + "HEAD_REQ_CCY, HEAD_REQ_BS, HEAD_REQ_VALDATE, HEAD_REQ_SETTLEAMT, HEAD_REQ_SETTLEFLAG, HEAD_REQ_SOURCE_REF) "
            + "VALUES(?, ?, ?, ?, ?, ?, ?,"
            +  "?, ?, ?, ?, ?, ?) ";
    
    private static final String UPDATE_RES = "" +
            "UPDATE " + TBLLOG + " SET " +
    		"RESP_CODE = ?, " +
            "RESP_DESC = ?, " +
    		"RESP_TIME = ?, " +
            "RESP_NO_OF_REC = ?, " +
			"DET_RESP_SETTLE_ERRSTATUS = ?, " +
			"DET_RESP_SETTLE_ERRDESC = ? " +
			"WHERE SER_LOG_ID = ? ";
    
    private static final String UPDATE_CCY1OUT = "" +
            "UPDATE " + TBLLOG + " SET " +              
			"DET_RESP_CCY1OUTS = ? " +
			"WHERE SER_LOG_ID = ? ";
           
    
    
	public static int logRequest(Connection conn, String system_id,
			String service_id, String request_id, int no_rec,
			String h_dealid, String h_cifno, String h_ccy, String h_bs,
			String h_valdate, BigDecimal h_amt, String h_flag, String h_src_ref)
			throws Exception {
    	
    	int id = 0;
    	PreparedStatement ppst = null;
    	ResultSet rs = null;
    	Date req_time = new Date();
    	
		try {
				ppst = conn.prepareStatement(INSERT_REQ, PreparedStatement.RETURN_GENERATED_KEYS);
								
				ppst.setTimestamp(1,new Timestamp(req_time.getTime()));
				ppst.setString(2,system_id);
				ppst.setString(3,service_id);
				ppst.setString(4, request_id);
				ppst.setInt(5, no_rec);
				ppst.setString(6,h_dealid);
				ppst.setString(7,h_cifno);
				ppst.setString(8, h_ccy);
				ppst.setString(9, h_bs);
				ppst.setString(10,h_valdate);
				ppst.setBigDecimal(11,h_amt);
				ppst.setString(12, h_flag);
				ppst.setString(13, h_src_ref);
				
				ppst.executeUpdate();	
				rs = ppst.getGeneratedKeys();
				if(rs.next()){
					id = rs.getInt(1);
				}
				
				conn.commit();
			
		} catch (SQLException ex) {
			// TODO Auto-generated catch block
			LogWriter.error("Class LogUtils [Method : logRequest] error -->>> " + ex.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		}  finally {
			if (ppst != null) {
                try {
                	ppst.close();
                } catch (SQLException ex) {
                	LogWriter.error("Class LogUtils [Method : logRequest] error -->>> " + ex.getMessage(), Constant.LOGGER_WEBSERVICE);
                	throw new Exception();
                }
            }
            if (rs != null) {
                try {
                	rs.close();
                } catch (SQLException ex) {
                	LogWriter.error("Class LogUtils [Method : logRequest] error -->>> " + ex.getMessage(), Constant.LOGGER_WEBSERVICE);
                	throw new Exception();
                }
            }
		}
		return id;       
    }  

	public static int logResponse(Connection conn, String respCode,
			String respDesc, int resp_noOfRec, String resp_settle_errstatus,
			String resp_settle_errdesc, int ser_log_id) throws Exception {
    	
    	int id = 0;
    	PreparedStatement ppst = null;
    	ResultSet rs = null;
    	Date resp_time = new Date();
    	
		try {
				ppst = conn.prepareStatement(UPDATE_RES);	
				
				ppst.setString(1, respCode); 
				ppst.setString(2, respDesc);
				ppst.setTimestamp(3,new Timestamp(resp_time.getTime()));
				ppst.setInt(4, resp_noOfRec);
				ppst.setString(5, resp_settle_errstatus); 
				ppst.setString(6, resp_settle_errdesc); 
				ppst.setInt(7, ser_log_id);
		
				ppst.executeUpdate();
				
				conn.commit();
			
		} catch (SQLException ex) {
			// TODO Auto-generated catch block
			LogWriter.error("Class LogUtils [Method : logResponse] error -->>> " + ex.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		}  finally {
			if (ppst != null) {
                try {
                	ppst.close();
                } catch (SQLException ex) {
                	LogWriter.error("Class LogUtils [Method : logResponse] error -->>> " + ex.getMessage(), Constant.LOGGER_WEBSERVICE);
                	throw new Exception();
                }
            }
            if (rs != null) {
                try {
                	rs.close();
                } catch (SQLException ex) {
                	LogWriter.error("Class LogUtils [Method : logResponse] error -->>> " + ex.getMessage(), Constant.LOGGER_WEBSERVICE);
                	throw new Exception();
                }
            }
		}
		return id;       
    } 
	
	public static int updateCCY1OUT(Connection conn, BigDecimal resp_ccy1out, int ser_log_id) throws Exception{
    	
    	int id = 0;
    	PreparedStatement ppst = null;
    	ResultSet rs = null;
    	
		try {
				ppst = conn.prepareStatement(UPDATE_CCY1OUT);	
				 				
				ppst.setBigDecimal(1, resp_ccy1out); // call store call_outstd
				ppst.setInt(2, ser_log_id);
		
				ppst.executeUpdate();
				
				conn.commit();
			
		} catch (SQLException ex) {
			// TODO Auto-generated catch block
			LogWriter.error("Class LogUtils [Method : updateCCY1OUT] error -->>> " + ex.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		}  finally {
			if (ppst != null) {
                try {
                	ppst.close();
                } catch (SQLException ex) {
                	LogWriter.error("Class LogUtils [Method : updateCCY1OUT] error -->>> " + ex.getMessage(), Constant.LOGGER_WEBSERVICE);
                	throw new Exception();
                }
            }
            if (rs != null) {
                try {
                	rs.close();
                } catch (SQLException ex) {
                	LogWriter.error("Class LogUtils [Method : updateCCY1OUT] error -->>> " + ex.getMessage(), Constant.LOGGER_WEBSERVICE);
                	throw new Exception();
                }
            }
		}
		return id;       
    } 
     
}
